#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin", "$FindBin::Bin");
use gene_loci;
use db_parameters;
use ensembl_parameters;

use DBI;
use DBD::Pg;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_gene_loci

	Description:
		Obtain ENSEMBL gene loci, i.e. cytogenetic location /
		     chromosome / strand / relative gene position
			 from the database or from the sequence file
	Populates DB tables:
		gene_loci

888888888888888888888888888888888888888888888888888888888888888888888
HEADLINE

use constant GENEID =>0;
use constant CHRMSM =>1;
use constant START_ =>2;
use constant END___ =>3;
use constant STRAND =>4;
use constant CENTRE =>5;
use constant ORDER_ =>5;

#_________________________________________________________________________________________

#	retrieve_ensembl_loci

#		retrieve data from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_ensembl_loci($)
{
	my ($taxon) = @_;
	{
		my $ens_db_name = $taxon->[ENS_TAXON] .'_core_'.$taxon->[ENS_VERSION];
		my $dbh_mysql = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
								{
									RaiseError => 1,
									AutoCommit => 1,
									PrintError => 0
								}
							)
		or die "Database connection not made: DBI::errstr()";
		my $array = $dbh_mysql->selectall_arrayref(<<'PL/SQLCMD');
        SELECT
			ensg.stable_id,
			sr.name,
			t.seq_region_start,
			t.seq_region_end,
			t.seq_region_strand
		FROM
			gene_stable_id ensg,
			gene g,
			transcript t,
			seq_region sr
		WHERE
			t.gene_id	= ensg.gene_id			AND
			t.gene_id	= g.gene_id   			AND
			t.seq_region_id = sr.seq_region_id;
PL/SQLCMD
        $dbh_mysql->disconnect;
		return $array;

	}

}





#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# Main logic

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

{
	
	#
	#	Remove previous error files
	#
	unlink "$dir_pipeline_errors/gene_transcripts.strands.errors";
	unlink "$dir_pipeline_errors/gene_transcripts.too_long.errors";
	
	#
	#	Get DB parameters
	#
	my @db_param = get_ensembl_taxon_data();
	my $dbh = connect_to_panda();
	$dbh->do("TRUNCATE taxon.gene_loci");
	for my $taxon_data(@db_param)
	{
		$dbh->do("COPY taxon.gene_loci".
					"(gene_id, chromosome, start, finish, strand, ".
						"gene_position, db_origin) FROM STDIN");
	
	
		print STDERR "\t$taxon_data->[TAXON] gene loci\n";
		my $transcript_loci;
		{
			#
			#	Retrieve from ENSEMBL
			#
			print STDERR "\t\tRetrieving from Ensembl...\n";
			$transcript_loci = retrieve_ensembl_loci($taxon_data);
	
		}
	
		#
		#	Merge transcript loci if same gene (gene_id) and sort by genomic location
		#		from chromosome 1 onwards
		#
		my (@transcript_strand_errors, @transcript_length_errors);
		copy_ordered_gene_loci_by_chromosome(	\$dbh,
												@$transcript_loci,
												@transcript_strand_errors,
												@transcript_length_errors,
												$taxon_data->[PANDA_DB_ORIGIN]);
	
		$dbh->pg_putline("\\.\n");
		$dbh->pg_endcopy;
		if (@transcript_strand_errors)
		{
			print STDERR "\t\t", '!' x 53, "\n\t\t\t", scalar @transcript_strand_errors,
				" genes had transcripts from different strands!!\n\t\t", '!' x 53, "\n";
	
			# print out a list of transcripts on the wrong strand
			open LOCI_ERRORS, ">>$dir_pipeline_errors/gene_transcripts.strands.errors";
			print LOCI_ERRORS join "\n", @transcript_strand_errors, "";
		}
	
		if (@transcript_length_errors)
		{
			print STDERR "\t\t", '!' x 53, "\n\t\t\t", scalar @transcript_length_errors,
				" genes were over 1 Mbp in length!!\n\t\t", '!' x 53, "\n";
	
			# print out a list of transcripts on the wrong strand
			open LOCI_ERRORS, ">>$dir_pipeline_errors/gene_transcripts.too_long.errors";
			print LOCI_ERRORS "gene_id\tlength\n-------\t------\n";
			for (@transcript_length_errors)
			{
				print LOCI_ERRORS $_->[0], "\t", $_->[1], "\n";
			}
		}
	
	}
	$dbh->disconnect;
	print STDERR "\tCompleted\n";
}

